package cn.modoumama.excelUilt.util;

import java.io.File;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.modoumama.common.tools.ObjectUtils;
import cn.modoumama.common.tools.PathUtils;
import cn.modoumama.excelUilt.common.Common;
import cn.modoumama.excelUilt.po.CellEntity;
import cn.modoumama.excelUilt.po.SheetEntity;

public class ReadExcel {
    private static Logger logger = LoggerFactory.getLogger(ReadExcel.class);
    private Workbook workbook;

    public ReadExcel() {
        super();
    }

    public Workbook getWorkbook() {
        return workbook;
    }

    public ReadExcel(InputStream in) {
        super();
        try {
            workbook = WorkbookFactory.create(in);

        } catch (Exception e) {
            logger.error("初始化读取文件失败！", e);
            throw new RuntimeException(e);
        }
    }

    public ReadExcel(String path) {
        super();
        try {
            init(path);
        } catch (Exception e) {
            logger.error("初始化读取文件失败！", e);
            throw new RuntimeException(e);
        }
    }


    /**
     * 获取单元格的内容
     *
     * @param sheet           sheet 页
     * @param listCombineCell 存放合并单元格的list
     * @param cell            需要判断的单元格
     * @return 获取有内容的单元格
     */
    private static Cell getCombineCell(Sheet sheet, List<CellRangeAddress> listCombineCell, Cell cell) {
        int firstC, lastC, firstR, lastR;
        if (cell != null) {
            for (CellRangeAddress ca : listCombineCell) {
                // 获得合并单元格的起始行, 结束行, 起始列, 结束列
                firstC = ca.getFirstColumn();
                lastC = ca.getLastColumn();
                firstR = ca.getFirstRow();
                lastR = ca.getLastRow();
                if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                    if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                        Row fRow = sheet.getRow(firstR);
                        cell = fRow.getCell(firstC);
                    }
                }
            }
        }
        return cell;
    }

    /**
     * 读取文件，并解析
     *
     * @param path 文件路径
     */
    private void init(String path) {
        path = path == null ? PathUtils.EMPTY : path.trim();
        if (!PathUtils.EMPTY.equals(path)) {
            String postfix = PathUtils.getPostfix(path);
            if (!PathUtils.EMPTY.equals(postfix) && (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix) || Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix))) {
                try {
                    workbook = WorkbookFactory.create(new File(path));
                } catch (Exception e) {
                    logger.error("文件夹读取失败", e);
                    throw new RuntimeException(e);
                }
            } else {
                logger.error("无效路径,或类型不正确");
                throw new RuntimeException("无效路径,或类型不正确");
            }
        } else {
            try {
                throw new RuntimeException("请传入文件路径");
            } catch (Exception e) {
                logger.error("文件名为空,请传入文件路径", e);
                throw new RuntimeException(e);
            }
        }
    }

    /**
     * 获取集合用String[]接收
     * List&lt;List&lt;String[]>> excelData = readExcel.readExcel();<br>
     * for (List&lt;String[]> excelSheet : excelData) {<br>
     * for (String[] row : excelSheet) {<br>
     * for (int i = 0, max = row.length-1; i &lt;= max; i++) {<br>
     * if( i < max){
     * System.out.print(row[i]+"\t");<br>
     * }else{
     * System.out.print(row[i]+"\r\n");<br>
     * }<br>
     * }<br>
     * }<br>
     * }<br>
     */
    public List<List<String[]>> readExcel() {
        List<List<String[]>> excelSheets = new ArrayList<>();
        List<CellRangeAddress> cellRanges;
        List<String[]> excelRows;
        for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = workbook.getSheetAt(numSheet);
            if (sheet != null) {
                cellRanges = getCombineCell(sheet);
                excelRows = new ArrayList<>();
                // 读取行
                for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    if (row != null) {
                        int cellNum = row.getLastCellNum();
                        String[] excelCells = new String[cellNum];
                        for (int i = 0; i < cellNum; i++) {
                            try {
                                excelCells[i] = getValue(String.class, getCombineCell(sheet, cellRanges, row.getCell(i)),null);
                            } catch (Exception e) {
                                logger.error("获取第" + (rowNum + 1) + "行，第" + (i + 1) + "列数据失败", e);
                                throw new RuntimeException(e);
                            }
                        }
                        excelRows.add(excelCells);
                    }
                }
                excelSheets.add(excelRows);
            }
        }
        return excelSheets;
    }

    /**
     * 自带表头，表头为属性名<br>
     *
     * @param clazz 需要封装的类型
     *              List&lt;SteelModel&gt; steels = readExcel.readFieldHeadExcel(SteelModel.class).get(0);
     * @return 封装的集合类型
     */
    public <T> List<T> readFieldHeadExcel(Class<T> clazz) {
        return readExcel(clazz);
    }


    /**
     * 获取集合并封装起来
     *
     * @param clazz  需要封装的类型
     * @param fields 每一列对象的需要封装的对象成员属性
     *               SheetEneity fields = new SheetEneity();<br>
     *               fields.put(0, "id");<br>
     *               fields.put(1, "name");<br>
     *               List&lt;SteelModel&gt; steels = readExcel.readNotHeadExcel(SteelModel.class, fields).get(0);<br>
     */
    public <T> List<T> readNotHeadExcel(Class<T> clazz, SheetEntity fields) {
        if (fields == null) {
            throw new RuntimeException("实体类对应关系（fields）为空！");
        }
        if (fields.isHead()) {
            throw new RuntimeException("实体类对应关系（fields）中不能包含描述信息！");
        }
        return readExcel(clazz, fields);
    }

    /**
     * 自带表描述和表头，表头为字段描述
     *
     * @param clazz     需要封装的类型
     * @param fieldMaps 属性和描述的对应关系
     *                  Map&lt;String, String&gt; fieldMaps = new HashMap&lt;&gt;();
     *                  fieldMaps.put("名称", "name");
     *                  fieldMaps.put("日期", "date");
     *                  fieldMaps.put("编号", "id");
     *                  List&lt;SteelModel&gt; steels = readExcel.readDescHradExcel(SteelModel.class, fieldMaps).get(0);
     * @return 封装的集合类型
     */
    public <T> List<T> readDescTableHradExcel(Class<T> clazz, Map<String, String> fieldMaps) {
        if (fieldMaps == null) {
            throw new RuntimeException("实体类对应关系（fieldMaps）不能为空！");
        }
        return readExcel(clazz, fieldMaps, true);
    }

    /**
     * 自带表头，表头为描述
     *
     * @param clazz     需要封装的类型
     * @param fieldMaps 属性和描述的对应关系
     *                  Map&lt;String, String&gt; fieldMaps = new HashMap&lt;&gt;();
     *                  fieldMaps.put("名称", "name");
     *                  fieldMaps.put("日期", "date");
     *                  fieldMaps.put("编号", "id");
     *                  List&lt;SteelModel&gt; steels = readExcel.readDescHradExcel(SteelModel.class, fieldMaps).get(0);
     * @return 封装好的集合类型
     */
    public <T> List<T> readDescHradExcel(Class<T> clazz, Map<String, String> fieldMaps) {
        if (fieldMaps == null) {
            throw new RuntimeException("实体类对应关系（fieldMaps）不能为空！");
        }
        return readExcel(clazz, fieldMaps, false);
    }

    /**
     * 自定义读取规则
     *
     * @param clazz  需要封装的类型
     * @param fields 每一列对象的需要封装的对象成员属性
     * @return 封装好的集合类型
     */
    public <T> List<T> readCustomExcel(Class<T> clazz, SheetEntity fields) {
        if (fields == null) {
            throw new RuntimeException("配置信息不能为空");
        }
        return readExcel(clazz, fields);
    }


    /**
     * 读取数据
     *
     * @param clazz     需要封装的类型
     * @param fieldMaps 属性和描述的对应关系
     * @param tableDesc 是否带描述
     * @return 封装好的集合类型
     */
    private <T> List<T> readExcel(Class<T> clazz, Map<String, String> fieldMaps, boolean tableDesc) {
        SheetEntity fields = new SheetEntity();
        fields.setHead(true);
        fields.setTableDesc(tableDesc);
        Sheet sheet = workbook.getSheetAt(0);
        // 读取表头
        Row row = sheet.getRow(fields.getHeadRowNumber());
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            String desc = cell.getStringCellValue();
            if (desc != null) {
                desc = desc.trim();
            }
            if (StringUtils.isNotBlank(desc)) {
                String field = fieldMaps.get(desc);
                if (StringUtils.isNotBlank(field)) {
                    fields.put(i, field, desc);
                }
            }
        }
        return readExcel(clazz, fields);
    }

    /**
     * 读取excel（带描述），并封装到制定类型的集合中
     *
     * @param clazz  需要封装的类型
     * @param fields 每一列对象的需要封装的对象成员属性
     * @param <T>    泛型
     * @return 封装好的集合类型
     */
    private <T> List<T> readExcel(Class<T> clazz, SheetEntity fields) {
        List<T> excelRows = new ArrayList<>();
        Sheet sheet = null;
        //读取页
        if(StringUtils.isNotBlank(fields.getName())){
            sheet = workbook.getSheet(fields.getName());
        }else{
            sheet = workbook.getSheetAt(fields.getIndex());
        }
        if (sheet != null) {
            excelRows = readRow(clazz, fields, sheet);
        }
        return excelRows;
    }

    /**
     * 读取excel，自带表头信息，表头信息为转换类型的属性名，并封装到制定类型的集合中
     *
     * @param clazz 需要封装的类型
     * @param <T>   泛型
     * @return 封装好的集合类型
     */
    private <T> List<T> readExcel(Class<T> clazz) {
        List<T> excelRows = new ArrayList<>();

        Sheet sheet = workbook.getSheetAt(0);
        if (sheet != null) {
            List<CellRangeAddress> cellRanges = getCombineCell(sheet);
            /*
             * 获取表头信息
             */

            SheetEntity fields = new SheetEntity();
            fields.setHead(true);

            Row head = sheet.getRow(0);
            if (head != null) {
                // 读取表头
                for (int cellNum = 0; cellNum < head.getLastCellNum(); cellNum++) {
                    try {
                        String field = getValue(String.class, getCombineCell(sheet, cellRanges, head.getCell(cellNum)),null);
                        if (StringUtils.isNotBlank(field)) {
                            fields.put(cellNum, field, "");
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }

            excelRows = readRow(clazz, fields, sheet);
        }
        return excelRows;
    }

    /**
     * 转换一页数据
     *
     * @param clazz  需要封装的类型
     * @param fields 每一列对象的需要封装的对象成员属性
     * @param sheet  一页数据
     * @param <T>    泛型
     * @return 封装好的集合类型
     */
    private <T> List<T> readRow(Class<T> clazz, SheetEntity fields, Sheet sheet) {
        List<T> excelRows = new ArrayList<>();
        List<CellRangeAddress> cellRanges = getCombineCell(sheet);
        // 读取行
        for (int rowNum = fields.getFirst(); rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                //根据class反射生产实体类
                T excelCells = null;
                try {
                    excelCells = clazz.newInstance();
                } catch (Exception e) {
                    logger.error("实例化" + clazz + "失败", e);
                }

                //是否有数据
                boolean flag = false;
                for (Integer i : fields.keySet()) {
                    //把值注入到实体内中
                	
                    try {
                    	Object value = getValue(ObjectUtils.getPropertyType(clazz, fields.getField(i)), getCombineCell(sheet, cellRanges, row.getCell(i)),fields);
                    	if(value != null) {
                    		ObjectUtils.setValue(fields.getField(i), value, excelCells);
                    		flag = true;
                    	}
                    }catch (Exception e) {
                        e.printStackTrace();
                        throw new RuntimeException(e);
                    }
                }
                if (flag) {
                    excelRows.add(excelCells);
                }
            }
        }

        return excelRows;
    }

    /**
     * 合并单元格处理--加入list
     *
     * @param sheet 一页数据
     * @return 单页合并单元格的集合
     */
    public List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<>();
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            // 获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 把单元格的内容转换成指定类型
     *
     * @param clazz 需要转换成的类型
     * @param cell  单元格
     * @return
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     * @throws SecurityException
     */
    @SuppressWarnings("all")
    private <T> T getValue(Class<T> clazz, Cell cell, SheetEntity fields) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {

        if (null != cell) {
            String value = "";
            DecimalFormat df = new DecimalFormat("#######.####");
            if(cell.getCellType() == CellType.STRING){
                value = cell.getRichStringCellValue().getString().trim();
            }else if(cell.getCellType() == CellType.NUMERIC){
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return (T) Common.parsDate(clazz, date);
                } else {
                    value = df.format(cell.getNumericCellValue());
                }
            }else if(cell.getCellType() == CellType.BOOLEAN){
                value = String.valueOf(cell.getBooleanCellValue()).trim();
            }else if(cell.getCellType() == CellType.FORMULA){
                value = cell.getCellFormula();
            }

            if(fields != null){
                CellEntity cellEntry = fields.get(cell.getColumnIndex()).getValue();
                if (cellEntry.getInitCell() != null) {
                    Object obj = cellEntry.getInitCell().getCallValue(cell, value);
                    if(obj != null){
                        Object val = cellEntry.getInitCell().getCallValue(cell, value);
                        if(clazz == val.getClass()){
                            return (T) val;
                        }
                        value = val+"";
                    }
                }
            }

            return ObjectUtils.parsValue(clazz, value);
        } else {
            return null;
        }
    }

}
